﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Enterprise.Dal
{
    #region 数据库操作类
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public sealed class DbHelperSQL
    {
        #region 执行SQL语句，无返回值
        /// <summary>
        /// 执行SQL语句，无返回值
        /// </summary>
        /// <param name="strSql"></param>
        public static int ExecuteSql(string strSql)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, CommandType.Text, strSql);
            }
            catch (SqlException e)
            {
                return 0;
            }
        }
        #endregion
        /// <summary>
        /// 执行一条没有返回数据结果集的 SqlCommand 
        /// </summary>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParameters">执行需要的参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteSql(string strSql, params SqlParameter[] commandParameters)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, CommandType.Text, strSql, commandParameters);
            }
            catch (System.Exception ex)
            {
                return 0;
            }
          
        }

        #region 执行SQL语句有返回值
        /// <summary>
        /// 执行SQL语句有返回值
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string strSql)
        {
            try
            {
                object val = SqlHelper.ExecuteScalar(SqlHelper.connectionString, CommandType.Text, strSql);
                if (val == System.DBNull.Value || val == null)
                {
                    return string.Empty;
                }
                else
                {
                    return val;
                }
            }
            catch (SqlException e)
            {
                return string.Empty; 
            }
        }
        #endregion



         /// <summary>
        /// 执行SQL语句有返回值
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string strSql, params SqlParameter[] parameterValues)
        {
            try
            {
                object val = SqlHelper.ExecuteScalar(SqlHelper.connectionString, CommandType.Text, strSql,parameterValues);
                if (val == System.DBNull.Value || val == null)
                {
                    return string.Empty;
                }
                else
                {
                    return val;
                }
            }
            catch (SqlException e)
            {
                return string.Empty; 
            }
        }
        #endregion

        #region 执行SQL返回Table
        /// <summary>
        /// 执行SQL返回Table
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            try
            {
                return SqlHelper.ExecuteDataset(SqlHelper.connectionString, CommandType.Text, sql).Tables[0];
            }
            catch (SqlException e)
            {
                return null;
            }
        }
        #endregion

        /// <summary>
        /// 执行SQL返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(string sql)
        {
            try
            {
                return SqlHelper.ExecuteDataset(SqlHelper.connectionString, CommandType.Text, sql);
            }
            catch (SqlException e)
            {
                return null;
            }
        }

        #region 
        /// <summary>
        /// 执行一条 SqlCommand 返回 DataTable 数据集
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        public static DataTable ExecuteDataTable(string commandText, params SqlParameter[] commandParameters)
        {
            try
            {
                return SqlHelper.ExecuteDataset(SqlHelper.connectionString, CommandType.Text, commandText, commandParameters).Tables[0];
            }
            catch (SqlException e)
            {
                return null;
            }
        }
        #endregion

        #region
        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        public static DataSet ExecuteDataSet(string commandText, params SqlParameter[] commandParameters)
        {
            try
            {
                return SqlHelper.ExecuteDataset(SqlHelper.connectionString, CommandType.Text, commandText, commandParameters);
            }
            catch (SqlException e)
            {
                return null;
            }
        }
        #endregion
         /// <summary>
        /// 执行一条存储过程返回 DataSet 数据集
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataTable ExecuteDataTable(string spName, params object[] parameterValues)
        {
            try
            {
                return SqlHelper.ExecuteDataset(SqlHelper.connectionString, spName, parameterValues).Tables[0];
            }
            catch (SqlException e)
            {
                return null;
            }
        }

        #region 执行查询语句，返回SqlDataReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            return SqlHelper.ExecuteReader(SqlHelper.connectionString, CommandType.Text, strSQL);
        }
        #endregion

        #region 执行多条SQL语句，实现数据库事务
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>	
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            return SqlHelper.ExecuteSqlTran(SQLStringList);
        }
        #endregion

        #region 分页
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Fields"></param>
        /// <param name="OrderField"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="TotalPage"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable PageProcedure(string TableName, string Fields, string OrderField, string sqlWhere, int pageSize, int pageIndex, out int TotalPage, out int totalRecord)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@TableName", SqlDbType.VarChar,5000),
                    new SqlParameter("@Fields", SqlDbType.VarChar,5000),
                    new SqlParameter("@OrderField", SqlDbType.VarChar,5000),
                    new SqlParameter("@sqlWhere", SqlDbType.VarChar,5000),
                    new SqlParameter("@pageSize", SqlDbType.Int,4),
                    new SqlParameter("@pageIndex", SqlDbType.Int,4),
                    new SqlParameter("@TotalPage", SqlDbType.Int,4),
                    new SqlParameter("@totalRecord", SqlDbType.Int,4)};

            parameters[0].Value = TableName;
            parameters[1].Value = Fields;
            parameters[2].Value = OrderField;
            parameters[3].Value = sqlWhere;
            parameters[4].Value = pageSize;
            parameters[5].Value = pageIndex;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.Output;

            string[] par = { "@TotalPage", "@totalRecord" };
            object[] rtValue;

            DataTable dt =SqlHelper.RunProcedure("pageView", parameters, "DataPage", par, out rtValue).Tables[0];
            if (dt.Rows.Count > 0)
            {
                TotalPage = int.Parse(rtValue[0].ToString());
                totalRecord = int.Parse(rtValue[1].ToString());
            }
            else
            {
                TotalPage = 0;
                totalRecord = 0;
            }
            return dt;
        }
        #endregion

        #region  增加的一个函数
        /// <summary>
        /// 运行SQl语句返回第一条记录的第一个字段的值。区分内部函数调用的情况
        /// </summary>
        /// <param name="text">SQL语句，或者参数化的SQL语句</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(string strSql)
        {
            try
            {
                T re = (T)Convert.ChangeType(SqlHelper.ExecuteScalar(SqlHelper.connectionString, CommandType.Text, strSql), typeof(T));
                return re;
            }
            catch (Exception ex)
            {
                return default(T);
            }
        }
        #endregion
    }
}